/********************************************/
  // Creation History
  // --------------------------------------------------------------
  //	Company:			HTHK
  //	NAME:				SystemBreakDown.java
  //    Title   :
  //	FUNCTION:
  //	BY:					Nick Huang
  //	DATE:				2013-06-26
  // --------------------------------------------------------------
/*********************************************/

package tss.systembreakdown;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;
import tss.common.FloatCalculator;

import com.ffpc.sql.DataException;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

public class SystemBreakDown extends DBServlet2
{
	private Logger logger = Logger.getLogger(SystemBreakDown.class);
	@Override
	protected String perform(HttpServletRequest request, HttpServletResponse response, 
			StrutsMessage message) throws ServletException, IOException, SQLException, DataException, Exception {
		
		String search_date_from = request.getParameter("date_from");
		String search_date_to = request.getParameter("date_to");
		
		StringBuffer reportSQL = new StringBuffer();
		
		reportSQL.append(" select project_category_name SYSTEM, ");
		reportSQL.append("        sum(decode(substr(project_name, 1, 1), 'C', spend, 0)) Development, ");
		reportSQL.append("        sum(decode(substr(project_name, 1, 1), 'S', spend, 0)) Support, ");
		reportSQL.append("        sum(decode(substr(project_name, 1, 1), 'I', spend, 0)) Incident, ");
		reportSQL.append("        sum(decode(substr(project_name, 1, 1), 'O', spend, 0)) Operation ");
		reportSQL.append("  from tss_v_project_report ");
		
		reportSQL.append("  where 1=1 ");
		if (search_date_from != null && search_date_from.length() > 0 && 
				search_date_to != null && search_date_to.length() > 0) {
			reportSQL.append("  and spend_date between to_date('" + search_date_from + "', 'yyyy-mm-dd') and to_date('" + search_date_to + "', 'yyyy-mm-dd') ");
		}
		
		reportSQL.append("   and project_name <> 'O-LEAVE' ");
		reportSQL.append(" group by project_category_name ");
		reportSQL.append(" order by project_category_name ");
		
		Statement stmt = null;
		Connection conn = null;
		ResultSet systemBreakDownReport = null;
		
		List reportList = new ArrayList();
	  	Map recordMap = null;
	  	String system = null;
	  	
	  	float development;
	  	float support;
	  	float incident;
	  	float operation;
	  	
	  	float developmentSummer = 0f;
	  	float supportSummer = 0f;
	  	float incidentSummer = 0f;
	  	float operationSummer = 0f;
	  	
	  	try {
	  		conn = this.getConnection();
			stmt = conn.createStatement();
			
			systemBreakDownReport = stmt.executeQuery(reportSQL.toString());
		  	
			while (systemBreakDownReport.next()) {
				recordMap = new HashMap();
				
				/*
				 * Get data
				 */
				system = systemBreakDownReport.getString(1);
				development = systemBreakDownReport.getFloat(2);
				support = systemBreakDownReport.getFloat(3);
				incident = systemBreakDownReport.getFloat(4);
				operation = systemBreakDownReport.getFloat(5);
				
				/*
				 * Sum every break down
				 */
				developmentSummer = FloatCalculator.add(developmentSummer, development);
				supportSummer = FloatCalculator.add(supportSummer, support);
				incidentSummer = FloatCalculator.add(incidentSummer, incident);
				operationSummer = FloatCalculator.add(operationSummer, operation);
				
				if (system != null && system.length() > 0) {
					recordMap.put("System", system);
				}
				recordMap.put("Development", new Float(development));
				recordMap.put("Support", new Float(support));
				recordMap.put("Incident", new Float(incident));
				recordMap.put("Operation", new Float(operation));
				
				reportList.add(recordMap);
			}
			
			systemBreakDownReport.close();
			stmt.close();
			
	  	} catch (Exception e) {
	  		logger.error(e, e);
	  		request.setAttribute("flag", "N");
	  		return "tss/systembreakdown/SystemBreakDownReport.jsp";
	  		
	  	} finally {
	  		JdbcUtil.release(stmt, systemBreakDownReport);
	  		
	  	}
		
		if (reportList != null && reportList.size() > 0) {
			
			/*
			 * Put the summary to the second last row
			 */
			recordMap = new HashMap();
			recordMap.put("System", "");
			recordMap.put("Development", new Float(developmentSummer));
			recordMap.put("Support", new Float(supportSummer));
			recordMap.put("Incident", new Float(incidentSummer));
			recordMap.put("Operation", new Float(operationSummer));
			reportList.add(recordMap);
			
			/*
			 * Put the percentage to the last row
			 */
			float summer = 0f;
			summer = FloatCalculator.add(summer, developmentSummer);
			summer = FloatCalculator.add(summer, supportSummer);
			summer = FloatCalculator.add(summer, incidentSummer);
			summer = FloatCalculator.add(summer, operationSummer);
			
			recordMap = new HashMap();
			recordMap.put("System", "");
			recordMap.put("Development", new Float(FloatCalculator.divide(developmentSummer, summer)));
			recordMap.put("Support", new Float(FloatCalculator.divide(supportSummer, summer)));
			recordMap.put("Incident", new Float(FloatCalculator.divide(incidentSummer, summer)));
			recordMap.put("Operation", new Float(FloatCalculator.divide(operationSummer, summer)));
			reportList.add(recordMap);
			
		}
		
		request.setAttribute("reportList", reportList);
		
		if (search_date_from != null && search_date_from.length() > 0) {
			request.setAttribute("date_from", search_date_from);
		}
		if (search_date_to != null && search_date_to.length() > 0) {
			request.setAttribute("date_to", search_date_to);
		}
		
		return "tss/systembreakdown/SystemBreakDownReport.jsp";
  }
	
}


